# coding=utf-8
# Written by YuelinZhang
import sys
import pymssql
import socket
import getpass
import datetime
import sip  # for pyinstaller
from PyQt5.QtWidgets import QApplication, QMainWindow, QMessageBox, QTableWidgetItem, QWidget, QHBoxLayout, QPushButton
from gui import gui_login, gui_login_help, gui_main1, gui_main1_help, gui_main1_update
from app_sql import SQLServer


class WindowLogin(QMainWindow, gui_login.Ui_Login):
    def __init__(self, sql):
        """
        :param sql: SQL server class
        """
        super(WindowLogin, self).__init__()
        # help window
        self._Window_help = QMainWindow()
        self._ui_help = gui_login_help.Ui_Login_help()
        self._ui_help.setupUi(self._Window_help)
        # login window
        self.Window = QMainWindow()
        self.setupUi(self.Window)
        self.Window.setFixedSize(440, 454)
        self.loginmode_set(auto=True)
        self.pushButton_1.clicked.connect(self.auto_login)
        self.pushButton_2.clicked.connect(lambda: self.loginmode_set(auto=False))
        self.pushButton_3.clicked.connect(self.close)
        self.pushButton1.clicked.connect(lambda: self.loginmode_set(auto=True))
        self.pushButton2.clicked.connect(self.manually_login)
        self.actionHelp.triggered.connect(self._Window_help.show)
        # sql server
        self.username = '{}\\{}'.format(socket.gethostname(), getpass.getuser())
        self.lineEdit1.setText(self.username)
        self.password = None
        self.ip = socket.gethostbyname(socket.gethostname())
        self.lineEdit.setText(self.ip)
        self.sql = sql
        self.show()

        # system window
        self._Window_system = None

    def manually_login(self):
        """
        manually login(read value from line edit)
        :return:
        """
        self.ip = self.lineEdit.text()
        self.username = self.lineEdit1.text()
        self.password = self.lineEdit2.text()
        sqlstatus = self.sql.login(self.ip, self.username, self.password)
        if sqlstatus == 1:
            QMessageBox.information(self, 'Success', 'Successfully Login! Welcome {}!'.format(self.username),
                                    QMessageBox.Ok)
            self._next_window()
        elif sqlstatus == 0:
            QMessageBox.information(self, 'Warning', 'Illegal input for username or password!', QMessageBox.Ok)
        elif sqlstatus == 2:
            QMessageBox.information(window_login, 'Warning',
                                    'Password or username or IP is incorrect! Or the service is stop.'
                                    'Click help for more information.',
                                    QMessageBox.Ok)
        else:
            QMessageBox.information(self, 'Warning', 'Connection error! Check your SQL Server setting '
                                                     'or restart the program', QMessageBox.Ok)

    def auto_login(self):
        """
        auto login
        :return:
        """
        sqlstatus = self.sql.login(self.ip, '', '')
        if sqlstatus == 1:
            QMessageBox.information(self, 'Success', 'Successfully Login! Welcome {}!'.format(self.username),
                                    QMessageBox.Ok)
            self._next_window()
        else:
            QMessageBox.information(self, 'Warning', 'Connection error! Please use manually login instead.', QMessageBox.Ok)

    def loginmode_set(self, auto=True):
        """
        set login mode
        :param auto: use auto login or not
        :return:
        """
        if auto:
            self.pushButton_1.setVisible(True)
            self.pushButton_2.setVisible(True)
            self.pushButton_3.setVisible(True)
            self.pushButton1.setVisible(False)
            self.pushButton2.setVisible(False)
            self.lineEdit1.setVisible(False)
            self.lineEdit2.setVisible(False)
            self.lineEdit.setVisible(False)
            self.label_2.setVisible(False)
            self.label_3.setVisible(False)
            self.label_8.setVisible(False)
        else:
            self.pushButton_1.setVisible(False)
            self.pushButton_2.setVisible(False)
            self.pushButton_3.setVisible(False)
            self.pushButton1.setVisible(True)
            self.pushButton2.setVisible(True)
            self.lineEdit1.setVisible(True)
            self.lineEdit2.setVisible(True)
            self.lineEdit.setVisible(True)
            self.label_2.setVisible(True)
            self.label_3.setVisible(True)
            self.label_8.setVisible(True)

    def _next_window(self):
        self.close()
        self._Window_system = WindowSystem(self.sql)

    def show(self):
        self.Window.show()

    def close(self):
        self.Window.close()


class WindowSystem(QMainWindow, gui_main1.Ui_System):
    def __init__(self, sql):
        """
        :param sql: SQL server class
        """
        super(WindowSystem, self).__init__()

        # Help window
        self._Window_help = QMainWindow()
        self._ui_help = gui_main1_help.Ui_System_help()
        self._ui_help.setupUi(self._Window_help)

        # Update window
        self._Window_update = None
        self._ui_update = None

        # SQL init
        self.numofrec = {'E':0, 'S':0, 'D':0}
        self.lastupdatetime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 'All' page last update time
        self.sql = sql
        status = self.sql.create_database()
        if status == 0:  # fail
            QMessageBox.information(self, 'Warning',
                                    'Please restart the system to reconnect to the SQL Server!\n请重启程序！', QMessageBox.Ok)
        elif status == 2:  # fail
            QMessageBox.information(self, 'Warning', 'OperationalError! Please restart the system.\n请重启程序！',
                                    QMessageBox.Ok)
        elif status == 1 or status == 3:  # success
            if status == 1:
                QMessageBox.information(self, 'Notice', 'Database is not exist. New database will be create.\n数据库不存在，将会创建新的数据库。',
                                        QMessageBox.Ok)
            # System window
            self.Window = QMainWindowSQL(self.sql.conn)
            self.setupUi(self.Window)
            self.actionHelp.triggered.connect(self._Window_help.show)
            self.pushButton_1.clicked.connect(self.button_EXITdelete_exit)
            self.pushButton_2.clicked.connect(self.button_EXITexit)
            self.pushButton_3.clicked.connect(self.button_checkConn)
            # Update table
            self.updateBasicinfo('E')
            self.updateBasicinfo('S')
            self.updateBasicinfo('D')
            # Insert button
            self.insertButton_E.clicked.connect(lambda: self.update_or_insert_Item('E'))
            self.insertButton_D.clicked.connect(lambda: self.update_or_insert_Item('D'))
            self.insertButton_S.clicked.connect(lambda: self.update_or_insert_Item('S'))
            # Create view
            self.createView()
            self.displayView()
            self.refreshButton.clicked.connect(self.displayView)
            # Show window (only when successfully init)
            self.show()

    def updateBasicinfo(self, table):
        """
        update information in 'basic' tab
        :param table: choose which table to update, 'E' or 'S' or 'D'
        :return: 1-success, 0-fail
        """

        if table is 'E':
            tabletoupdate = self.tableWidget_E
        elif table is 'S':
            tabletoupdate = self.tableWidget_S
        elif table is 'D':
            tabletoupdate = self.tableWidget_D
        else:
            return 0
        items = self.sql.select(table)
        if items == 2:
            QMessageBox.information(self, 'Warning',
                                    'OperationalError! Please retry or restart the system!\n请重试或重启程序！', QMessageBox.Ok)
            return 0
        # elif not items:
        #
        #     return 1
        self.lastupdatetime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # update lastupdatetime
        self.numofrec[table] = len(items)  # update num of rec
        self.label_6.setText('Last update at {} \nEmployees: {} records, Salary: {} records, Departments: {} records.'
                             .format(self.lastupdatetime, self.numofrec['E'], self.numofrec['S'], self.numofrec['D']))
        self.label_8.setText('最近一次更新： {} \nEmployees: {}条记录, Salary: {}条记录, Departments: {}条记录。'
                             .format(self.lastupdatetime, self.numofrec['E'], self.numofrec['S'], self.numofrec['D']))
        tabletoupdate.clearContents()
        tabletoupdate.setSortingEnabled(False)
        tabletoupdate.setRowCount(len(items))
        for row in range(tabletoupdate.rowCount()):
            for column in range(tabletoupdate.columnCount()):
                if column != 0:
                    item = QTableWidgetItem(str(items[row][column - 1]))  # TODO 数据类型为str，会导致数值无法正常排序
                    tabletoupdate.setItem(row, column, item)
                else:
                    tabletoupdate.setCellWidget(row, column, self.buttonForRow(table, items[row][0]))  # add button
        tabletoupdate.setSortingEnabled(True)
        return 1

    def buttonForRow(self, table, key):
        """
        add button to table
        :param table: 'E' or 'S' or 'D'
        :param key: primary key of the table
        :return:
        """
        widget = QWidget()
        # Update button
        updateBtn = QPushButton('Update(修改)')
        updateBtn.setStyleSheet(''' text-align : center;
                                          background-color : DarkSeaGreen;
                                          height : 30px;
                                          border-style: outset;
                                          font : 12px  ''')
        updateBtn.clicked.connect(lambda: self.update_or_insert_Item(table, key))

        # delete button
        deleteBtn = QPushButton('Delete(删除)')
        deleteBtn.setStyleSheet(''' text-align : center;
                                    background-color : LightCoral;
                                    height : 30px;
                                    border-style: outset;
                                    font : 12px; ''')
        deleteBtn.clicked.connect(lambda: self.deleteItem(table, key))

        hLayout = QHBoxLayout()
        hLayout.addWidget(updateBtn)
        hLayout.addWidget(deleteBtn)
        hLayout.setContentsMargins(5, 2, 5, 2)
        widget.setLayout(hLayout)
        return widget

    def update_or_insert_Item(self, table, key=None):
        """
        update record or insert a new record
        :param key: key of the record to be updated, or use None to insert a new one
        :param table: choose which table to insert, 'E' or 'S' or 'D'
        :return:
        """
        self._Window_update = QMainWindow()
        self._ui_update = gui_main1_update.Ui_Update()
        self._ui_update.setupUi(self._Window_update)
        self._ui_update.pushButton_1.clicked.connect(self._Window_update.close)
        if table is 'E':
            if key is not None:
                rec = self.sql.select(table, key=key, keyname='EmployeeID')
                self._ui_update.lineEdit_1.setText(str(rec[0][0]))
                self._ui_update.lineEdit_2.setText(str(rec[0][1]))
                self._ui_update.lineEdit_3.setText(str(rec[0][2]))
                self._ui_update.lineEdit_4.setText(str(rec[0][3]))
                self._ui_update.lineEdit_5.setText(str(rec[0][6]))
                self._ui_update.lineEdit_6.setText(str(rec[0][7]))
                self._ui_update.lineEdit_7.setText(str(rec[0][8]))
        elif table is 'S':
            self._ui_update.label_8.setText('Salary Table (正数为应发，负数为应扣)')
            self._ui_update.label_2.setText('Amount(数额)')
            self._ui_update.label_3.setText('')
            self._ui_update.label_4.setText('')
            self._ui_update.label_5.setText('')
            self._ui_update.label_6.setText('')
            self._ui_update.label_7.setText('')
            self._ui_update.label_3.setEnabled(False)
            self._ui_update.label_4.setEnabled(False)
            self._ui_update.label_5.setEnabled(False)
            self._ui_update.label_6.setEnabled(False)
            self._ui_update.label_7.setEnabled(False)
            self._ui_update.lineEdit_3.setEnabled(False)
            self._ui_update.lineEdit_4.setEnabled(False)
            self._ui_update.lineEdit_5.setEnabled(False)
            self._ui_update.lineEdit_6.setEnabled(False)
            self._ui_update.lineEdit_7.setEnabled(False)
            if key is not None:
                rec = self.sql.select(table, key=key, keyname='ID')
                self._ui_update.lineEdit_1.setText(str(rec[0][1]))
                self._ui_update.lineEdit_2.setText(str(rec[0][2]))
        elif table is 'D':
            self._ui_update.label_8.setText('Departments Table (请谨慎修改部门号)')
            self._ui_update.label.setText('DepartmentID(3位部门号)')
            self._ui_update.label_2.setText('DepartmentName(部门名称)')
            self._ui_update.label_3.setText('Note(注)')
            self._ui_update.label_4.setText('')
            self._ui_update.label_5.setText('')
            self._ui_update.label_6.setText('')
            self._ui_update.label_7.setText('')
            self._ui_update.label_4.setEnabled(False)
            self._ui_update.label_5.setEnabled(False)
            self._ui_update.label_6.setEnabled(False)
            self._ui_update.label_7.setEnabled(False)
            self._ui_update.lineEdit_4.setEnabled(False)
            self._ui_update.lineEdit_5.setEnabled(False)
            self._ui_update.lineEdit_6.setEnabled(False)
            self._ui_update.lineEdit_7.setEnabled(False)
            if key is not None:
                rec = self.sql.select(table, key=key, keyname='DepartmentID')
                self._ui_update.lineEdit_1.setText(str(rec[0][0]))
                self._ui_update.lineEdit_2.setText(str(rec[0][1]))
                self._ui_update.lineEdit_3.setText(str(rec[0][2]))
        else:
            return 0
        if key is not None:
            self._ui_update.pushButton_2.clicked.connect(lambda: self._update(table, rec))
        else:
            self._ui_update.pushButton_2.clicked.connect(lambda: self._insert(table))
        self._Window_update.show()

    def _update(self, table, rec):
        """
        update a record in the table
        :param table: 'E' 'S' 'D'
        :param rec: old record
        :return:
        """
        rec = list(rec[0])
        newrec = []
        if table is 'E':
            rec.pop(4)
            rec.pop(4)
            column = ['EmployeeID', 'Name', 'Sex', 'WorkYear', 'DepartmentID', 'Position', 'Rank']
            newrec.append(self._ui_update.lineEdit_1.text())
            if len(newrec[0]) != 6:
                QMessageBox.information(self, 'Warning', 'Please input EmployeeID in correct form!\n请输入正确格式的员工号！(6位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_2.text())
            newrec.append(self._ui_update.lineEdit_3.text())
            newrec.append(int(self._ui_update.lineEdit_4.text()))
            if int(newrec[3]) < 0:
                QMessageBox.information(self, 'Warning', 'Please input WorkYear in correct form!\n请输入正确格式的工龄！(非负数)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_5.text())
            if len(newrec[4]) != 3:
                QMessageBox.information(self, 'Warning',
                                        'Please input DepartmentID in correct form!\n请输入正确格式的部门号！(3位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_6.text())
            newrec.append(self._ui_update.lineEdit_7.text())
            cmd = ''
            for i, item in enumerate(rec):
                if item != newrec[i]:
                    if i != 3:
                        cmd = cmd + ' {}=\'{}\','.format(column[i], newrec[i])
                    else:
                        cmd = cmd + ' {}={},'.format(column[i], newrec[i])
            if cmd != '':
                cmd = cmd[:-1]
                try:
                    self.sql.cursor.execute('UPDATE Employees SET {} WHERE EmployeeID={}'.format(cmd, rec[0]))
                except pymssql.IntegrityError as e:
                    if str(e).find('PRIMARY') != -1:
                        QMessageBox.information(self, 'Warning',
                                                'Please check your input of EmployeeID!\n请检查员工号的输入！(员工号重复)',
                                                QMessageBox.Ok)
                    elif str(e).find('REFERENCE') != -1 and str(e).find('Salary') != -1:
                        QMessageBox.information(self, 'Warning',
                                                'Violation of REFERENCE constraint!\n员工号修改违反参照完整性规则！(请先删除Salary中该员工的记录)',
                                                QMessageBox.Ok)
                    elif str(e).find('FOREIGN') != -1 and str(e).find('Depar') != -1:
                        QMessageBox.information(self, 'Warning',
                                                'Violation of DepartmentID!\n部门号修改违反参照完整性规则！(不存在的部门，请先添加对应部门信息)',
                                                QMessageBox.Ok)
                    return 1
                except pymssql.OperationalError:
                    QMessageBox.information(self, 'Warning', 'OperationalError!\nSQL运行错误！请检查输入', QMessageBox.Ok)
                    return 1
        elif table is 'S':
            key = rec[0]
            rec.pop(0)
            column = ['EmployeeID', 'Amount', 'Note']
            newrec.append(self._ui_update.lineEdit_1.text())
            if len(newrec[0]) != 6:
                QMessageBox.information(self, 'Warning', 'Please input EmployeeID in correct form!\n请输入正确格式的员工号！(6位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(float(self._ui_update.lineEdit_2.text()))
            if newrec[1] == 0:
                QMessageBox.information(self, 'Warning', 'Please input Amount in correct form!\n请输入正确格式的数额！(非零数)',
                                        QMessageBox.Ok)
                return 1
            elif rec[-1] == '应发' and (newrec[1] < 0):
                QMessageBox.information(self, 'Warning', 'Please input Amount in correct form!\n请输入正确格式的数额！(应发数额为正数)',
                                        QMessageBox.Ok)
                return 1
            elif rec[-1] == '应扣' and newrec[1] > 0:
                QMessageBox.information(self, 'Warning', 'Please input Amount in correct form!\n请输入正确格式的数额！(应扣数额为负数)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(rec[-1])
            cmd = ''
            for i, item in enumerate(rec):
                if item != newrec[i]:
                    if i != 1:
                        cmd = cmd + ' {}=\'{}\','.format(column[i], newrec[i])
                    else:
                        cmd = cmd + ' {}={},'.format(column[i], newrec[i])
            if cmd != '':
                cmd = cmd[:-1]
                try:
                    self.sql.cursor.execute('UPDATE Salary SET {} WHERE ID={}'.format(cmd, key))
                except pymssql.IntegrityError as e:
                    if str(e).find('FOREIGN') != -1:
                        QMessageBox.information(self, 'Warning',
                                                'Please check your input of EmployeeID!\n请检查员工号的输入！(员工号不存在)',
                                                QMessageBox.Ok)
                    return 1
                except pymssql.OperationalError:
                    QMessageBox.information(self, 'Warning', 'OperationalError!\nSQL运行错误！请检查输入', QMessageBox.Ok)
                    return 1
        elif table is 'D':
            column = ['DepartmentID', 'DepartmentName', 'Note']
            newrec.append(self._ui_update.lineEdit_1.text())
            if len(newrec[0]) != 3:
                QMessageBox.information(self, 'Warning',
                                        'Please input DepartmentID in correct form!\n请输入正确格式的部门号！(3位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_2.text())
            newrec.append(self._ui_update.lineEdit_3.text())
            cmd = ''
            for i, item in enumerate(rec):
                if item != newrec[i]:
                    cmd = cmd + ' {}=\'{}\','.format(column[i], newrec[i])

            if cmd != '':
                cmd = cmd[:-1]
                try:
                    print('UPDATE Salary SET {} WHERE ID={}'.format(cmd, rec[0]))
                    self.sql.cursor.execute('UPDATE Departments SET {} WHERE DepartmentID={}'.format(cmd, rec[0]))
                except pymssql.IntegrityError as e:
                    if str(e).find('PRIMARY') != -1:
                        QMessageBox.information(self, 'Warning',
                                                'Please check your input of DepartmentID!\n请检查部门号的输入！(部门号重复)',
                                                QMessageBox.Ok)
                    elif str(e).find('REFERENCE') != -1:
                        QMessageBox.information(self, 'Warning',
                                                'Violation of REFERENCE constraint!\n部门号修改违反参照完整性规则！(请先删除Employees中相应员工的记录)',
                                                QMessageBox.Ok)
                    return 1
                except pymssql.OperationalError:
                    QMessageBox.information(self, 'Warning', 'OperationalError!\nSQL运行错误！请检查输入', QMessageBox.Ok)
                    return 1
        else:
            return 0
        self._Window_update.close()
        self.updateBasicinfo('E')
        self.updateBasicinfo('S')
        self.updateBasicinfo('D')

    def _insert(self, table):
        """
        insert a record into table
        :param table: 'E' 'S' 'D'
        :return:
        """
        newrec = []
        if table is 'E':
            newrec.append(self._ui_update.lineEdit_1.text())
            if len(newrec[0]) != 6:
                QMessageBox.information(self, 'Warning', 'Please input EmployeeID in correct form!\n请输入正确格式的员工号！(6位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_2.text())
            newrec.append(self._ui_update.lineEdit_3.text())
            newrec.append(int(self._ui_update.lineEdit_4.text()))
            if int(newrec[3]) < 0:
                QMessageBox.information(self, 'Warning', 'Please input WorkYear in correct form!\n请输入正确格式的工龄！(非负数)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_5.text())
            if len(newrec[4]) != 3:
                QMessageBox.information(self, 'Warning',
                                        'Please input DepartmentID in correct form!\n请输入正确格式的部门号！(3位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_6.text())
            newrec.append(self._ui_update.lineEdit_7.text())
            newrec.insert(4, 0)
            newrec.insert(4, 0)
            status = self.sql.insert(table, newrec)
            if status == 0:  # fail
                QMessageBox.information(self, 'Warning',
                                        'Please restart the system to reconnect to the SQL Server!\n请重启程序！',
                                        QMessageBox.Ok)
                return 1
            elif status == 3:
                QMessageBox.information(self, 'Warning', 'OperationalError!\nSQL运行错误！请检查输入', QMessageBox.Ok)
                return 1
            elif status == 4:
                QMessageBox.information(self, 'Warning',
                                        'Please check your input of EmployeeID!\n请检查员工号的输入！(员工号重复)', QMessageBox.Ok)
                return 1
            elif status == 5:
                QMessageBox.information(self, 'Warning',
                                        'Violation of REFERENCE constraint!\n员工号修改违反参照完整性规则！(请先删除Salary中该员工的记录)',
                                        QMessageBox.Ok)
                return 1
            elif status == 6:
                QMessageBox.information(self, 'Warning',
                                        'Violation of DepartmentID!\n部门号修改违反参照完整性规则！(不存在的部门，请先添加对应部门信息)',
                                        QMessageBox.Ok)
                return 1
        elif table is 'S':
            newrec.append(self._ui_update.lineEdit_1.text())
            if len(newrec[0]) != 6:
                QMessageBox.information(self, 'Warning', 'Please input EmployeeID in correct form!\n请输入正确格式的员工号！(6位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(float(self._ui_update.lineEdit_2.text()))
            if newrec[1] == 0:
                QMessageBox.information(self, 'Warning', 'Please input Amount in correct form!\n请输入正确格式的数额！(非零数)',
                                        QMessageBox.Ok)
                return 1
            elif newrec[1] < 0:
                newrec.append('应扣')
            elif newrec[1] > 0:
                newrec.append('应发')
            status = self.sql.insert(table, newrec)
            if status == 0:  # fail
                QMessageBox.information(self, 'Warning',
                                        'Please restart the system to reconnect to the SQL Server!\n请重启程序！',
                                        QMessageBox.Ok)
                return 1
            elif status == 3:
                QMessageBox.information(self, 'Warning', 'OperationalError!\nSQL运行错误！请检查输入', QMessageBox.Ok)
                return 1
            elif status == 6:
                QMessageBox.information(self, 'Warning',
                                        'Please check your input of EmployeeID!\n请检查员工号的输入！(员工号不存在)',
                                        QMessageBox.Ok)
                return 1
        elif table is 'D':
            newrec.append(self._ui_update.lineEdit_1.text())
            if len(newrec[0]) != 3:
                QMessageBox.information(self, 'Warning',
                                        'Please input DepartmentID in correct form!\n请输入正确格式的部门号！(3位数字)',
                                        QMessageBox.Ok)
                return 1
            newrec.append(self._ui_update.lineEdit_2.text())
            newrec.append(self._ui_update.lineEdit_3.text())
            status = self.sql.insert(table, newrec)
            if status == 0:  # fail
                QMessageBox.information(self, 'Warning',
                                        'Please restart the system to reconnect to the SQL Server!\n请重启程序！',
                                        QMessageBox.Ok)
                return 1
            elif status == 3:
                QMessageBox.information(self, 'Warning', 'OperationalError!\nSQL运行错误！请检查输入', QMessageBox.Ok)
                return 1
            elif status == 4:
                QMessageBox.information(self, 'Warning',
                                        'Please check your input of DepartmentID!\n请检查部门号的输入！(部门号重复)',
                                        QMessageBox.Ok)
                return 1
            elif status == 5:
                QMessageBox.information(self, 'Warning',
                                        'Violation of REFERENCE constraint!\n部门号修改违反参照完整性规则！(请先删除Employees中相应员工的记录)',
                                        QMessageBox.Ok)
                return 1
        else:
            return 0
        self._Window_update.close()
        self.updateBasicinfo('E')
        self.updateBasicinfo('S')
        self.updateBasicinfo('D')

    def deleteItem(self, table, key):
        """
        delete item in database and update the table display
        :param table: 'E' or 'S' or 'D'
        :param key: primary key of the table
        :return:
        """
        rec = QMessageBox.question(self, "Delete?", "Are you sure to delete the item?",
                                   QMessageBox.Yes | QMessageBox.No)
        if rec != 65536:
            if table == 'E':
                rec = QMessageBox.information(self, 'Warning',
                                              'IntegrityError! Conflicted with the REFERENCE constraint. '
                                              'Enforce deleting will delete the related records in Salary where EmployeeID={}. Continue?\n'
                                              '参照完整性冲突! 强制执行将会删除Salary表中所有EmployeeID={}的数据。是否继续？'.format(key, key),
                                              QMessageBox.Yes | QMessageBox.No)
                if rec != 65536:
                    self.sql.cursor.execute('ALTER TABLE Salary DISABLE TRIGGER all')
                    self.sql.delete('S', key, 'EmployeeID')
                    self.sql.delete('E', key)
                    self.sql.cursor.execute('ALTER TABLE Salary ENABLE TRIGGER all')
                    self.updateBasicinfo('E')
                    self.updateBasicinfo('S')
            elif table == 'D':
                rec = QMessageBox.information(self, 'Warning',
                                              'IntegrityError! Conflicted with the REFERENCE constraint. '
                                              'Enforce deleting will delete the related records in Salary and Employees where DepartmentID={}. Continue?\n'
                                              '参照完整性冲突! 强制执行将会删除Salary表和Employees表中所有DepartmentID={}的数据。是否继续？'.format(
                                                  key, key),
                                              QMessageBox.Yes | QMessageBox.No)
                if rec != 65536:
                    s = self.sql.cursor.execute(
                        'SELECT DISTINCT EmployeeID FROM Employees WHERE DepartmentID={}'.format(key))
                    s = self.sql.cursor.fetchall()
                    self.sql.cursor.execute('ALTER TABLE Salary DISABLE TRIGGER all')
                    for item in s:
                        self.sql.delete('S', item[0], 'EmployeeID')
                        self.sql.delete('E', item[0])
                    self.sql.delete('D', key)
                    self.sql.cursor.execute('ALTER TABLE Salary ENABLE TRIGGER all')
                    self.updateBasicinfo('E')
                    self.updateBasicinfo('D')
                    self.updateBasicinfo('S')
            elif table == 'S':
                self.sql.delete('S', key)
                self.updateBasicinfo('E')
                self.updateBasicinfo('S')
            else:
                return 0

    def createView(self):
        """
        create a salary view or update the view (if it already exist)
        :return: 0-create, 1-update
        """
        try:
            self.sql.cursor.execute('USE TESTZ')
            self.sql.cursor.execute('CREATE VIEW SalaryView (EmployeeID, SalaryDue, SalaryDeduct, SalaryReal) AS '
                                    'SELECT DISTINCT Salary.EmployeeID, SalaryDue, SalaryReal-SalaryDue, SalaryReal '
                                    'From Salary, Employees WHERE Employees.EmployeeID = Salary.EmployeeID')
        except pymssql.OperationalError as e:
            if str(e).find('SalaryView') != -1:
                self.sql.cursor.execute('USE TESTZ')
                self.sql.cursor.execute('DROP VIEW SalaryView')
                self.sql.cursor.execute('CREATE VIEW SalaryView (EmployeeID, SalaryDue, SalaryDeduct, SalaryReal) AS '
                                        'SELECT DISTINCT Salary.EmployeeID, SalaryDue, SalaryReal-SalaryDue, SalaryReal '
                                        'From Salary, Employees WHERE Employees.EmployeeID = Salary.EmployeeID')
                return 1
        else:
            return 0

    def displayView(self):
        """
        display the Salary View
        :return: 1
        """
        self.sql.cursor.execute('SELECT * FROM SalaryView')
        items = self.sql.cursor.fetchall()
        self.tableWidget_View.clearContents()
        self.tableWidget_View.setSortingEnabled(False)
        self.tableWidget_View.setRowCount(len(items))
        for row in range(self.tableWidget_View.rowCount()):
            for column in range(self.tableWidget_View.columnCount()):
                item = QTableWidgetItem(str(items[row][column]))  # TODO 数据类型为str，会导致数值无法正常排序
                self.tableWidget_View.setItem(row, column, item)
        self.tableWidget_View.setSortingEnabled(True)
        self.label_3.setText('Detailed Salary information (工资详细信息) Last update at {}'
                             .format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
        return 1

    def button_checkConn(self):
        """
        Button: check connection, if connection lost, then exit the program
        :return:
        """
        status = self.sql.check_connection()
        if status:
            QMessageBox.information(self, 'Notice', 'Connection succeeded!\n连接正常！', QMessageBox.Ok)
        else:
            QMessageBox.information(self, 'Warning', 'Connection lost! Please restart the system!\n连接异常！请重启系统！', QMessageBox.Ok)
            self.Window.updateExitstatus(True)
            self.close()

    def button_EXITdelete_exit(self):
        """
        Button: delete and exit
        :return:
        """
        rec = QMessageBox.question(self, "Exit?", "Are you sure to delete database and exit? Warning: this operation CANNOT be undone!\n"
                                                  "你确定要删除数据库并退出吗？警告：此操作无法撤销！", QMessageBox.Yes | QMessageBox.No)
        if rec != 65536:
            try:
                self.sql.cursor.execute('ALTER database TESTZ set single_user with rollback immediate')
                self.sql.cursor.execute('USE MASTER DROP DATABASE TESTZ')
            except:
                QMessageBox.question(self, "Warning", "Delete failed! Please delete database manually in SQL Server.\n"
                                                      "删除失败！请在SQL Server中手动删除。", QMessageBox.Yes | QMessageBox.No)
            self.Window.updateExitstatus(True)
            self.close()

    def button_EXITexit(self):
        """
        Button: only exit
        :return:
        """
        rec = QMessageBox.question(self, "Exit?", "Are you sure to exit?", QMessageBox.Yes | QMessageBox.No)
        if rec != 65536:
            self.Window.updateExitstatus(True)
            self.close()

    def show(self):
        self.Window.show()

    def close(self):
        self.Window.close()


class QMainWindowSQL(QMainWindow):
    def __init__(self, conn):
        super(QMainWindow, self).__init__()
        self.cursor = conn.cursor()
        self.exitflag = False  # determine whether to show MessageBox

    def closeEvent(self, event):
        if self.exitflag is False:
            rec = QMessageBox.question(self, "Exit?", "Are you sure to exit?", QMessageBox.Yes | QMessageBox.No)
            if rec == 65536:
                event.ignore()
            else:
                event.accept()
        else:
            event.accept()

    def updateExitstatus(self, status):
        self.exitflag = status


if __name__ == '__main__':
    app = QApplication(sys.argv)

    sql_origin = SQLServer()
    window_login = WindowLogin(sql_origin)

    sys.exit(app.exec_())
